package impexp.exportfile;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;

import javax.sql.DataSource;

import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;

import com.aote.sql.SqlServer;
import com.aote.util.ExcelUtil;
import com.aote.util.XMLReaderUtil;

public class ExportExport implements IExportFile {

	private Map<String, String> map = new HashMap<String, String>();
	private String sqlname;
	@Autowired
	private DataSource dataSource;

	private static final int MAX_ROWS_PER_SHEET = 10001;
	private static final String defaultCondition = "{orderitem: 'id',condition: '1=1'}";

	public Map<String, String> getMap() {
		return map;
	}

	public void setMap(Map<String, String> map) {
		this.map = map;
	}

	public String getSqlname() {
		return sqlname;
	}

	public void setSqlname(String sqlname) {
		this.sqlname = sqlname;
	}

	@Override
	public JSONArray exportFile(String condition) throws Exception {
		Set<String> templateNames = map.keySet();
		return templateNames.size() == 0 ? noTemplate(condition) : hasTemplate(
				templateNames, condition);
	}

	private JSONArray noTemplate(String condition) throws Exception {
		JSONArray result = new JSONArray();
		JSONObject json = new JSONObject();
		long tick = System.currentTimeMillis();

		String filepath = getFilePath(new JSONObject(condition).getString("templateName")+".xlsx");
		json.put("filename", filepath);
		result.put(json);

		export(this.sqlname, condition, null, filepath);

		tick = System.currentTimeMillis() - tick;
		System.out.println("进行Excel导出耗时：" + (tick / 1000.0) + "秒");
		System.out.println("文件名：" + result);
		return result;
	}

	private JSONArray hasTemplate(Set<String> templateNames, String condition)
			throws Exception {
		JSONArray result = new JSONArray();

		int i = 0;
		for (String template : templateNames) {
			String sqlName = map.get(template);
			String filepath = getFilePath(template);
			JSONObject json = new JSONObject();
			json.put("filename", filepath);
			result.put(json);
			// 模板名 template
			template = "excel/" + template;
			// sql名 sqlName
			long tick = System.currentTimeMillis();

			if (i == 0) {
				export(sqlName, condition, template, filepath);
			} else {
				export(sqlName, defaultCondition, template, filepath);
			}

			tick = System.currentTimeMillis() - tick;
			System.out.println("进行Excel导出耗时：" + (tick / 1000.0) + "秒");
			System.out.println("文件名：" + result);
		}

		return result;
	}

	private String getFilePath(String name) {
		String excelFileName = (name == null ? UUID.randomUUID() + ".xlsx" : name);
		String path = ExcelUtil.class.getClassLoader().getResource("sql.xml")
				.getPath();
		String rootPath = path.split("WEB-INF")[0];
		String filePath = rootPath + "excel/" + excelFileName;
		return filePath;
	}

	/**
	 * Real export stuff
	 * 
	 * @param name
	 * @param jsonString
	 * @param templateName
	 * @param fileName
	 * @throws Exception
	 */
	private void export(String name, String body, String templateName,
			String filePath) throws Exception {

		JSONObject joParam = new JSONObject(body);

		String[][] footer = null;
		if (joParam.has("total")) {
			JSONArray totals = joParam.getJSONArray("total");
			footer = getFooter(totals);
		}

		// 获取原始sql语句
		JSONObject joVariable = joParam.getJSONObject("data");
		String sql = new SqlServer().call(name, joVariable);

		// open connection
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;

		try {
			// fetch data begins
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			rs = preparedStatement.executeQuery();
			rs.setFetchSize(MAX_ROWS_PER_SHEET);
			if (templateName == null)
				exportWithHedear(filePath, joParam, sql, rs, footer);
			else
				exportWithTemplate(templateName, sql, filePath, rs, footer);
		} finally {
			if (rs != null)
				rs.close();
			if (preparedStatement != null)
				preparedStatement.close();
			if (connection != null)
				connection.close();
		}
	}

	/**
	 * get footer lines
	 * 
	 * @param totals
	 * @return
	 */
	private String[][] getFooter(JSONArray totals) {
		int n = totals.length();
		String[][] footer = new String[n][];
		for (int i = 0; i < n; i++) {
			JSONObject jo = totals.getJSONObject(i);
			String[] names = JSONObject.getNames(jo);
			Arrays.sort(names, new Comparator<String>() {
				@Override
				public int compare(String o1, String o2) {
					return Integer.parseInt(o1) - Integer.parseInt(o2);
				}
			});
			footer[i] = new String[names.length];
			for (int j = 0; j < names.length; j++) {
				if (jo.isNull(names[j]))
					footer[i][j] = null;
				else
					footer[i][j] = jo.get(names[j]) + "";
			}
		}
		return footer;
	}

	/**
	 * export with template
	 * 
	 * @param templateName
	 * @param sql
	 * @param filePath
	 * @param footer
	 * @throws Exception
	 */
	private void exportWithTemplate(String templateName, String sql,
			String filePath, ResultSet rs, String[][] footer) throws Exception {
		// creating excel starts
		ExcelUtil eu = new ExcelUtil();
		eu.createBook(templateName, filePath);
		int nCol = rs.getMetaData().getColumnCount();
		int n = 1;
		List<List<Object>> rows = new ArrayList<List<Object>>();
		while (rs.next()) {
			if (n % MAX_ROWS_PER_SHEET == 0) {
				eu.createSheet();
				n = 1;
				eu.writeToSheet(rows, null, footer);
				rows = new ArrayList<List<Object>>();
			}
			List<Object> fieldList = new ArrayList<Object>();
			for (int i = 1; i <= nCol; i++) {
				Object object = rs.getObject(i);
				fieldList.add(object);
			}
			rows.add(fieldList);
			n++;
		}
		// not enough to reach the max row limit
		if (n > 1) {
			eu.createSheet();
			eu.writeToSheet(rows, null, footer);
		}
		eu.saveBook();
	}

	/**
	 * export with header info
	 * 
	 * @param filePath
	 * @param joParam
	 * @param sql
	 * @param rs
	 * @param footer
	 * @throws Exception
	 * @throws SQLException
	 */
	private void exportWithHedear(String filePath, JSONObject joParam,
			String sql, ResultSet rs, String[][] footer) throws Exception,
			SQLException {
		boolean fromHbm = false;

		// find out the headers
		JSONObject joField = joParam.getJSONObject("field");
		Map<String, String> colsMap = new LinkedHashMap<String, String>();
		if (joField.has("xmlPath")) {
			findOutHeaders(joField.getString("xmlPath"), sql, colsMap);
			fromHbm = true;
		} else {
			findOutHeaders(joField, colsMap);
		}
		String[] header = colsMap.values().toArray(new String[colsMap.size()]);
		String[] cols = colsMap.keySet().toArray(new String[colsMap.size()]);

		// creating excel starts
		ExcelUtil eu = new ExcelUtil();
		eu.createBook(null, filePath);

		int n = 1;
		List<List<Object>> rows = new ArrayList<List<Object>>();
		while (rs.next()) {
			if (n % MAX_ROWS_PER_SHEET == 0) {
				eu.createSheet();
				n = 1;
				eu.writeToSheet(rows, header, footer);
				rows = new ArrayList<List<Object>>();
			}
			List<Object> fieldList = new ArrayList<Object>();
			if (fromHbm)
				fieldList.add(rs.getObject("id"));
			for (int i = (fromHbm ? 1 : 0); i < colsMap.size(); i++) {
				Object object = rs.getObject(cols[i]);
				fieldList.add(object);
			}
			rows.add(fieldList);
			n++;
		}
		// not enough to reach the max row limit
		if (n > 1) {
			eu.createSheet();
			eu.writeToSheet(rows, header, footer);
		}
		eu.saveBook();
	}

	/**
	 * find out headers
	 * 
	 * @param joField
	 * @param headers
	 */
	private void findOutHeaders(JSONObject joField, Map<String, String> headers) {
		Iterator<String> it = joField.keySet().iterator();
		while (it.hasNext()) {
			String col = (String) it.next();
			headers.put(col, joField.getString(col));
		}
	}

	/**
	 * find out headers by parsing hbm configuration
	 * 
	 * @param hbmFile
	 * @param sql
	 * @param headers
	 */
	private void findOutHeaders(String hbmFile, String sql,
			Map<String, String> headers) {
		LinkedHashMap<Object, Object> comment = new XMLReaderUtil()
				.getCommont(hbmFile);
		headers.put("id", "编号");
		Iterator<Object> it = comment.keySet().iterator();
		while (it.hasNext()) {
			String col = (String) it.next();
			headers.put(col, comment.get(col) + "");
		}
	}

}
